package org.jeecg.modules.online.cgreport.service.impl;

import java.sql.SQLException;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

import org.apache.commons.lang.StringUtil;
import org.apache.commons.lang.StringUtils;
import org.jeecg.common.api.vo.Result;
import org.jeecg.common.constant.CacheConstant;
import org.jeecg.common.constant.DataBaseConstant;
import org.jeecg.common.exception.JeecgBootException;
import org.jeecg.common.system.api.ISysBaseAPI;
import org.jeecg.common.system.query.QueryGenerator;
import org.jeecg.common.system.vo.DictModel;
import org.jeecg.common.system.vo.DynamicDataSourceModel;
import org.jeecg.common.util.oConvertUtils;
import org.jeecg.common.util.dynamic.db.DataSourceCachePool;
import org.jeecg.common.util.dynamic.db.DynamicDBUtil;
import org.jeecg.modules.online.cgform.enums.DataBaseEnum;
import org.jeecg.modules.online.cgform.util.CgformUtil;
import org.jeecg.modules.online.cgreport.def.CgReportConstant;
import org.jeecg.modules.online.cgreport.entity.OnlCgreportHead;
import org.jeecg.modules.online.cgreport.entity.OnlCgreportItem;
import org.jeecg.modules.online.cgreport.entity.OnlCgreportParam;
import org.jeecg.modules.online.cgreport.mapper.OnlCgreportHeadMapper;
import org.jeecg.modules.online.cgreport.model.OnlCgreportModel;
import org.jeecg.modules.online.cgreport.service.IOnlCgreportHeadService;
import org.jeecg.modules.online.cgreport.service.IOnlCgreportItemService;
import org.jeecg.modules.online.cgreport.service.IOnlCgreportParamService;
import org.jeecg.modules.online.cgreport.util.CgReportSqlFiledParseUtils;
import org.jeecg.modules.online.cgreport.util.CgReportSqlUtil;
import org.jeecg.modules.online.config.exception.DBException;
import org.jeecg.modules.online.config.model.OnlineFieldConfig;
import org.jeecg.modules.online.config.util.ConditionHandler;
import org.jeecg.modules.online.config.util.DbTableUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;

import cn.hutool.core.util.ReUtil;
import lombok.extern.slf4j.Slf4j;

/**
 * @Description: 在线报表配置
 * @Author: jeecg-boot
 * @Date: 2019-03-08
 * @Version: V1.0
 */
@Service("onlCgreportHeadServiceImpl")
@Slf4j
public class OnlCgreportHeadServiceImpl extends ServiceImpl<OnlCgreportHeadMapper, OnlCgreportHead> implements IOnlCgreportHeadService {

	@Autowired
	private IOnlCgreportParamService onlCgreportParamService;
	@Autowired
	private IOnlCgreportItemService onlCgreportItemService;
	@Autowired
	private OnlCgreportHeadMapper mapper;
	@Autowired
	private ISysBaseAPI sysBaseAPI;

	/**
	 * 执行 select sql语句
	 *
	 * @param sql
	 */
	@Override
	public Map<String, Object> executeSelectSql(String sql, String onlCgreportHeadId, Map<String,Object> params) throws SQLException {
		String dbType = null;
		try {
			dbType = DbTableUtil.getDatabaseType();
		} catch (DBException e) {
			e.printStackTrace();
		}
		//1.先处理sql中的参数
		LambdaQueryWrapper<OnlCgreportParam> paramQuery = new LambdaQueryWrapper<OnlCgreportParam>();
		paramQuery.eq(OnlCgreportParam::getCgrheadId, onlCgreportHeadId);
    	List<OnlCgreportParam> paramList = onlCgreportParamService.list(paramQuery);
    	if(paramList!=null && paramList.size()>0) {
    		for (OnlCgreportParam p : paramList) {
    			//前端传值为了避免 SQL自带的参数和查询条件参数弄混了 故而将SQL自带的参数名加前缀:self_
    			Object value = params.get("self_"+p.getParamName());
    			String valueStr ="";
    			if(value!=null) {
    				valueStr = value.toString();
    			}else if(value==null && StringUtil.isNotEmpty(p.getParamValue())) {
    				valueStr = p.getParamValue();
    			}

    			//update-begin-author:taoyan date:20200916 for: popup 组件支持动态参数
    			String paramExpress = "${"+p.getParamName()+"}";
    			if(sql.indexOf(paramExpress)>0){
    				if(valueStr!=null && valueStr.startsWith("'") && valueStr.endsWith("'")){
						valueStr = valueStr.substring(1,valueStr.length()-1);
					}
    				//如果sql语句中有 参数表达式直接替换就行
					sql = sql.replace(paramExpress, valueStr);
				}else if(StringUtil.isNotEmpty(valueStr)){
    				// 如果sql语句中没有 并且该参数不为空 则视作popup参数
					params.put(CgReportConstant.POPUP_PARAM_PRE+p.getParamName(), valueStr);
				}
				//update-end-author:taoyan date:20200916 for: popup 组件支持动态参数
			}
    	}
		Map<String, Object> result = new HashMap<>();
		//2.分页查询逻辑
		Integer pageSize = oConvertUtils.getInt(params.get("pageSize"),10);
		Integer pageNo = oConvertUtils.getInt(params.get("pageNo"),1);
		Page<Map<String,Object>> page = new Page<>(pageNo,pageSize);
		//3.获取查询条件sql
		LambdaQueryWrapper<OnlCgreportItem> query = new LambdaQueryWrapper<OnlCgreportItem>();
		query.eq(OnlCgreportItem::getCgrheadId, onlCgreportHeadId);

		// update-begin--Author:sunjianlei---- Date:20210513 ----for：判断是否有强制查询条件 ----
		List<String> forceQuery = new ArrayList<>();
		for (String key : params.keySet().toArray(new String[]{})) {
			if (key.startsWith(CgReportConstant.POPUP_FORCE_PRE)) {
				String forceKey = key.substring(CgReportConstant.POPUP_FORCE_PRE.length());
				forceQuery.add(forceKey);
				params.put(forceKey, params.get(key));
			}
		}
		List<OnlCgreportItem> fieldList;
		if (forceQuery.size() > 0) {
			query.in(OnlCgreportItem::getFieldName, forceQuery);
			fieldList = onlCgreportItemService.list(query);
			fieldList.forEach(i -> i.setIsSearch(1));
		} else {
			query.eq(OnlCgreportItem::getIsSearch, 1);
			fieldList = onlCgreportItemService.list(query);
		}
		// update-end--Author:sunjianlei---- Date:20210513 ----for：判断是否有强制查询条件 ----

		//sql处理- 将基础sql中的系统变量替换掉
		// update-begin-author:taoyan date:20210916 for:online报表查询条件拼接改造
		// TODO 系统变量中文 sqlserver查询
		sql = QueryGenerator.convertSystemVariables(sql);
		List<OnlineFieldConfig> fieldConfigs = new ArrayList<>();
		for(OnlCgreportItem item: fieldList){
			fieldConfigs.add(new OnlineFieldConfig(item));
		}
		String alias = "jeecg_rp_temp.";
		ConditionHandler handler = new ConditionHandler(alias, dbType);
		// sql处理- 获取查询条件
		String condition = handler.getConditionSql(fieldConfigs, params);
		Map<String,Object> sqlParam = handler.getSqlParams();

		//check sqlserver 不允许sql内含 order by排序片段
		if (ReUtil.contains(" order\\s+by ", sql.toLowerCase())) {
			if (DataBaseConstant.DB_TYPE_SQLSERVER.equalsIgnoreCase(dbType)) {
				throw new JeecgBootException("SqlServer不支持SQL内排序!");
			}
		}
		// update-begin-author:taoyan date:20211015 for: 条件拼接去掉1=1
		String querySql = "select * from ("+sql+") jeecg_rp_temp ";
		if(condition.trim().length()>0){
			querySql+=" where " + condition;
		}
		// update-end-author:taoyan date:20211015 for: 条件拼接去掉1=1
		//加载数据权限配置 和 系统内置变量
	//	querySql = CgReportSqlUtil.rewriteReportSql(querySql);
		Object orderColumn = params.get("column");
		if(orderColumn!=null) {
			// update-begin-author:sunjianlei date:20211213 for: 【/issues/3243】支持多个排序条件（使用相同的排序顺序）
			String order = String.valueOf(params.get("order"));
			String[] orderColumns = String.valueOf(orderColumn).split(",");
			String joinColumn = String.join(" " + order + ", jeecg_rp_temp.", orderColumns);
			querySql += " order by jeecg_rp_temp." + joinColumn + " " + order;
			// update-end-author:sunjianlei date:20211213 for: 【/issues/3243】支持多个排序条件（使用相同的排序顺序）
		}
		//4.执行查询
		//log.info("报表查询sql=>\r\n"+querySql);
		//log.info("报表查询sqlParam=>\r\n"+sqlParam.toString());
		// 判断是否查询所有数据，否就分页
		IPage<Map<String,Object>> pageList;
		if (Boolean.valueOf(String.valueOf(params.get("getAll")))) {
			//List<Map<String, Object>> resList = this.mapper.executeSelect(querySql);
			List<Map<String, Object>> resList = this.mapper.selectByCondition(querySql, sqlParam);
			pageList = new Page<>();
			pageList.setRecords(resList);
			pageList.setTotal(resList.size());
		} else {
			pageList = this.mapper.selectPageByCondition(page, querySql, sqlParam);
			//pageList = this.mapper.selectPageBySql(page, querySql);
		}
		// update-end-author:taoyan date:20210916 for:online报表查询条件拼接改造
		result.put("total", pageList.getTotal());
		//此处无论什么数据库，都把查询结果key转为小写
		result.put("records",CgformUtil.toLowerCasePageList(pageList.getRecords()));
		return result;
	}

    /**
     * 执行 select sql语句
     *
     * @param sql
     */
    @Override
    @SuppressWarnings("unchecked")
    public Map<String, Object> executeSelectSqlDynamic(String dbKey, String sql, Map<String, Object> params, String onlCgreportHeadId) {
		String order = (String) params.get("order");
		String column = (String) params.get("column");
        int pageNo = oConvertUtils.getInt(params.get("pageNo"), 1);
        int pageSize = oConvertUtils.getInt(params.get("pageSize"), 10);
		//log.info("【Online多数据源逻辑】报表查询参数params: " + JSON.toJSONString(params));


		//1.Check校验 sqlserver 不允许sql内含 order by排序
		DynamicDataSourceModel dbSource = DataSourceCachePool.getCacheDynamicDataSourceModel(dbKey);
		if (ReUtil.contains(" order\\s+by ", sql.toLowerCase())) {
			if ("3".equalsIgnoreCase(dbSource.getDbType())) {
				throw new JeecgBootException("SqlServer不支持SQL内排序!");
			}
		}

		//2.先处理sql中的参数 ${name}
		LambdaQueryWrapper<OnlCgreportParam> paramQuery = new LambdaQueryWrapper<OnlCgreportParam>();
		paramQuery.eq(OnlCgreportParam::getCgrheadId, onlCgreportHeadId);
		List<OnlCgreportParam> paramList = onlCgreportParamService.list(paramQuery);
		if(paramList!=null && paramList.size()>0) {
			for (OnlCgreportParam p : paramList) {
				//前端传值为了避免 SQL自带的参数和查询条件参数弄混了 故而将SQL自带的参数名加前缀:self_
				Object value = params.get("self_"+p.getParamName());
				String valueStr ="";
				if(value!=null) {
					valueStr = value.toString();
				}else if(value==null && StringUtil.isNotEmpty(p.getParamValue())) {
					valueStr = p.getParamValue();
				}
				sql = sql.replace("${"+p.getParamName()+"}", valueStr);
			}
		}

		//3.获取页面查询条件
		LambdaQueryWrapper<OnlCgreportItem> query = new LambdaQueryWrapper<OnlCgreportItem>();
		query.eq(OnlCgreportItem::getCgrheadId,onlCgreportHeadId);
		query.eq(OnlCgreportItem::getIsSearch, 1);
		List<OnlCgreportItem> fieldList = onlCgreportItemService.list(query);

		sql = QueryGenerator.convertSystemVariables(sql);
		List<OnlineFieldConfig> fieldConfigs = new ArrayList<>();
		for(OnlCgreportItem item: fieldList){
			fieldConfigs.add(new OnlineFieldConfig(item));
		}
		String alias = "jeecg_rp_temp.";
		String realDataBaseType = DataBaseEnum.getDataBaseNameByValue(dbSource.getDbType());
		ConditionHandler handler = new ConditionHandler(alias, realDataBaseType);
		handler.setDaoType("jdbcTemplate");
		// sql处理- 获取查询条件
		String condition = handler.getConditionSql(fieldConfigs, params);
		Map<String,Object> sqlParam = handler.getSqlParams();
		//4.获取查询数据sql
		// update-begin-author:taoyan date:20211015 for: 条件拼接去掉1=1
		String querySql = "select * from ("+sql+") jeecg_rp_temp ";
		if(condition.trim().length()>0){
			querySql+=" where " + condition;
		}
		// update-end-author:taoyan date:20211015 for: 条件拼接去掉1=1
		//加载数据权限配置、系统上下文变量
		//querySql = CgReportSqlUtil.rewriteReportSql(querySql);
		//5.获取查询总记录数SQL
		String countSql = CgReportSqlUtil.getCountSql(querySql);

		//6.排序逻辑
		Object orderColumn = params.get("column");
		if(orderColumn!=null) {
			querySql+= " order by jeecg_rp_temp."+orderColumn.toString()+" "+params.get("order").toString();
		}

		//8.查询返回结果集
        Map<String, Object> result = new HashMap<>();
		//log.info("多数据源 报表查询sql=>querySql: " + querySql);
		//log.info("多数据源 报表查询sql=>countSql: " + countSql);
        //分页数据总条数查询
        Map<String, Object> queryCount = DynamicDBUtil.queryCount(dbKey, countSql, sqlParam);
        result.put("total", queryCount.get("total"));
		//根据不同的数据库，返回不同分页数据
		List<Map<String,Object>> dataList = CgReportSqlUtil.getAutoPageList(String.valueOf(params.get("getAll")), dbKey, querySql, pageNo, pageSize, sqlParam);
        result.put("records", CgformUtil.toLowerCasePageList(dataList));
        return result;
    }

	/** 修改全部项，包括新增、修改、删除 */
	@Override
	@Transactional(rollbackFor = Exception.class)
	public Result<?> editAll(OnlCgreportModel values) {
		OnlCgreportHead head = values.getHead();

		OnlCgreportHead onlCgreportHeadEntity = super.getById(head.getId());
		if (onlCgreportHeadEntity == null) {
			return Result.error("未找到对应实体");
		}

		super.updateById(head);

		//TODO 需要改造
		// update-begin--Author:sunjianlei Date:20190912 for：修复sql解析后数据保存失败的问题
		// 先删除所有子项
		LambdaQueryWrapper<OnlCgreportItem> queryWrapper1 = new LambdaQueryWrapper<>();
		queryWrapper1.eq(OnlCgreportItem::getCgrheadId, head.getId());
		onlCgreportItemService.remove(queryWrapper1);

		LambdaQueryWrapper<OnlCgreportParam> queryWrapper2 = new LambdaQueryWrapper<>();
		queryWrapper2.eq(OnlCgreportParam::getCgrheadId, head.getId());
		onlCgreportParamService.remove(queryWrapper2);

        // 再插入子项
        for (OnlCgreportParam param : values.getParams()) {
            param.setCgrheadId(head.getId());
        }
        for (OnlCgreportItem item : values.getItems()) {
        	//字段默认保存为小写(为了兼容各种数据库)
        	item.setFieldName(item.getFieldName().trim().toLowerCase());
            item.setCgrheadId(head.getId());
        }

        onlCgreportItemService.saveBatch(values.getItems());
        onlCgreportParamService.saveBatch(values.getParams());
        return Result.ok("全部修改成功");
		// update-end--Author:sunjianlei Date:20190912 for：修复sql解析后数据保存失败的问题
	}


	@Override
	@Transactional(rollbackFor = Exception.class)
	public Result<?> delete(String id) {
		boolean ok = super.removeById(id);
		if (ok) {
			//删除子表数据
			LambdaQueryWrapper<OnlCgreportItem> queryWrapper1 = new LambdaQueryWrapper<>();
			queryWrapper1.eq(OnlCgreportItem::getCgrheadId, id);
			onlCgreportItemService.remove(queryWrapper1);

			LambdaQueryWrapper<OnlCgreportParam> queryWrapper2 = new LambdaQueryWrapper<>();
			queryWrapper2.eq(OnlCgreportParam::getCgrheadId, id);
			onlCgreportParamService.remove(queryWrapper2);
		}
		return Result.ok("删除成功");
	}

	@Override
	@Transactional(rollbackFor = Exception.class)
	public Result<?> bathDelete(String[] ids) {
		for(String id:ids){
			boolean ok = super.removeById(id);
			if (ok) {
				//删除子表数据
				LambdaQueryWrapper<OnlCgreportItem> queryWrapper1 = new LambdaQueryWrapper<>();
				queryWrapper1.eq(OnlCgreportItem::getCgrheadId, id);
				onlCgreportItemService.remove(queryWrapper1);

				LambdaQueryWrapper<OnlCgreportParam> queryWrapper2 = new LambdaQueryWrapper<>();
				queryWrapper2.eq(OnlCgreportParam::getCgrheadId, id);
				onlCgreportParamService.remove(queryWrapper2);
			}
		}
		return Result.ok("删除成功");
	}

	/**
	 * 动态数据源： 获取SQL解析的字段
	 */
	@Override
	public List<String> getSqlFields(String sql,String dbKey) throws SQLException, DBException {
		List<String> fields = null;
		if(org.apache.commons.lang.StringUtil.isNotBlank(dbKey)){
			fields = this.parseSql(sql,dbKey);
		}else{
			fields = this.parseSql(sql,null);
		}
		return fields;
	}


	/**
	 * 解析SQL参数
	 */
	@Override
	public List<String> getSqlParams(String sql) {
		if(oConvertUtils.isEmpty(sql)){
			return null;
		}
		List<String> params = new ArrayList<String>();
		String regex = "\\$\\{\\w+\\}";

		Pattern p = Pattern.compile(regex);
		Matcher m = p.matcher(sql);
		while(m.find()){
			String whereParam = m.group();
			params.add(whereParam.substring(whereParam.indexOf("{")+1,whereParam.indexOf("}")));
		}
		return params;
	}

	/**
	 * 动态数据源： 获取SQL解析的字段
	 * @param sql
	 * @param dbKey
	 * @return
	 */
	private List<String> parseSql(String sql,String dbKey) throws SQLException, DBException {
		if(oConvertUtils.isEmpty(sql)){
			return null;
		}
		//update-begin-author:scott  date:20200324  for:LOWCOD-327 发现 online 报表好鸡肋啊，稍微复杂一点的sql就一直 sql解析失败
		//step.1 去掉结尾的;和空格

		//update-begin-author:scott  date:20210202  for:JT-489 online报表，sql参数解析问题
		//update-begin-author:taoyan date:20210909 for:Online报表配置-SQL解析，不支持 “ >= ” #2985
		sql = sql.replace("[^><]="," = ");
		//update-end-author:taoyan date:20210909 for:Online报表配置-SQL解析，不支持 “ >= ” #2985
		//update-end-author:scott  date:20210202  for:JT-489 online报表，sql参数解析问题
		sql = sql.trim();
		if (sql.endsWith(";")) {
			sql = sql.substring(0, sql.length() - 1);
		}

		//step2. 替换sql中的上下文变量
		sql = QueryGenerator.convertSystemVariables(sql);

		//step3. 判断sql中是否存在${}参数格式
		sql = CgReportSqlUtil.getUniversalSql(sql);
		//update-end-author:scott  date:20200324  for:LOWCOD-327 发现 online 报表好鸡肋啊，稍微复杂一点的sql就一直 sql解析失败

		//step.4 判断数据源类型，写不同的分页SQL（只查询一条，提高查询效率）
		Set<String> fieldsSet = null;
		if (org.apache.commons.lang.StringUtil.isNotBlank(dbKey)) {
			//log.info("parse sql : "+sql);
			DynamicDataSourceModel dbSource = DataSourceCachePool.getCacheDynamicDataSourceModel(dbKey);
			// check sqlserver 不允许sql内含 order by排序片段
			if (ReUtil.contains(" order\\s+by ", sql.toLowerCase())) {
				if ("3".equalsIgnoreCase(dbSource.getDbType())) {
					throw new JeecgBootException("SqlServer不支持SQL内排序!");
				}
			}

//			//update-begin-author:taoyan date:20210706 for:Online 报表 多数据源 在在线生成封装代码中 未加入5.7+ 判断
//			//组装不同数据库，分页SQL
//			//TODO 兼容的数据库太少
//			if (DbType.MYSQL.equals(dbtype) || DbType.MARIADB.equals(dbtype) || DbType.POSTGRE_SQL.equals(dbtype)) {
//				sql = "SELECT * FROM (" + sql + ") temp LIMIT 1";
//			} else if (DbType.ORACLE.equals(dbtype) || DbType.ORACLE_12C.equals(dbtype)) {
//				sql = "SELECT * FROM (" + sql + ") temp WHERE ROWNUM <= 1";
//			} else if (DbType.SQL_SERVER.equals(dbtype) || DbType.SQL_SERVER2005.equals(dbtype)) {
//				sql = "SELECT TOP 1 * FROM (" + sql + ") temp";
//			}
			//update-end-author:taoyan date:20210706 for:Online 报表 多数据源 在在线生成封装代码中 未加入5.7+ 判断


			//通过通用的分页sql，获取一条数据
			Map<String, Object> objectMap = CgReportSqlUtil.parseOneByPageSql(dbKey,sql);
			if (objectMap == null) {
				if(!sql.contains("*")){
					try {
						objectMap = CgReportSqlFiledParseUtils.SqlFiledResolution(sql);
					} catch (Exception e) {}
				}
				if(objectMap == null){
					throw new JeecgBootException("该报表sql没有数据");
				}
			}
			fieldsSet = objectMap.keySet();
		} else {
			//log.info("parse sql: "+sql);
			// check sqlserver 不允许sql内含 order by排序片段
			String dbType = DbTableUtil.getDatabaseType();
			if (ReUtil.contains(" order\\s+by ", sql.toLowerCase())) {
				if (DataBaseConstant.DB_TYPE_SQLSERVER.equalsIgnoreCase(dbType)) {
					throw new JeecgBootException("SqlServer不支持SQL内排序!");
				}
			}
			//组装分页SQL
			IPage<Map<String, Object>> pageList = this.mapper.selectPageBySql(new Page<>(1, 1), sql);
			List<Map<String, Object>> result = pageList.getRecords();
			if (result.size() < 1) {
				if(!sql.contains("*")){
					try {
						fieldsSet = CgReportSqlFiledParseUtils.SqlFiledResolution(sql).keySet();
					} catch (Exception e) {}
				}
				if(fieldsSet==null){
					throw new JeecgBootException("该报表sql没有数据");
				}
			}else{
				fieldsSet = result.get(0).keySet();
			}

		}


		//返回解析的字段
		//sql解析字段，去掉 Oracle数据库默认关键词 ROW_ID
		if (fieldsSet != null) {
			fieldsSet.remove("ROW_ID");
		}
		return new ArrayList<>(fieldsSet);
	}

	@Override
	public Map<String, Object> queryCgReportConfig(String reportId) {
		Map<String,Object> cgReportM = new HashMap<String, Object>(0);
		Map<String,Object> mainM = mapper.queryCgReportMainConfig(reportId);
		List<Map<String,Object>> itemsM = mapper.queryCgReportItems(reportId);
        List<OnlCgreportParam> params = mapper.queryCgReportParams(reportId);
		//update-begin-author:taoyan date:20190930 for:oracle数据库 online报表导出异常
		if(DbTableUtil.hasLetterCaseProblem()){
			cgReportM.put(CgReportConstant.MAIN, CgformUtil.transLowerCaseMapKey(mainM));
			cgReportM.put(CgReportConstant.ITEMS, CgformUtil.toLowerCasePageList(itemsM));
		}else{
			cgReportM.put(CgReportConstant.MAIN, mainM);
			cgReportM.put(CgReportConstant.ITEMS,itemsM);
		}
		//update-end-author:taoyan date:20190930 for:oracle数据库 online报表导出异常
		cgReportM.put(CgReportConstant.PARAMS, params);
		return cgReportM;
	}

//	/**
//	 * 分页查询报表数据
//	 */
//	@Override
//	public List<Map<?, ?>> queryByCgReportSql(String sql, Map params,Map paramData,
//			int pageNo, int pageSize) {
//		String querySql = SqlUtil.getFullSql(sql,params);
//		List<Map<?, ?>> result = null;
//		// TODO：这里的作用是什么？如果没用就删了
//		if(paramData!=null&&paramData.size()==0){
//			paramData = null;
//		}
//		if(pageNo==-1 && pageSize==-1){
//			result = mapper.executeSelete(querySql);
//		}else{
//			Page<Map<String,Object>> page = new Page<>(pageNo,pageSize);
//			IPage<Map<String,Object>> pageList = this.mapper.selectPageBySql(page, querySql);
//			if(pageList.getRecords()!=null && pageList.getRecords().size()>0) {
//				result.addAll(pageList.getRecords());
//			}
//		}
//		return result;
//	}

	@Override
	@Deprecated
	public List<DictModel> queryDictSelectData(String sql, String keyword) {
		List<DictModel> ls = new ArrayList<>();
		Page<Map<String,Object>> page = new Page<>();
		page.setSearchCount(false);
		page.setCurrent(1);
		page.setSize(10);
		// 去除空格和最后一个分号
		sql = sql.trim();
		int lastIndex = sql.lastIndexOf(";");
		if (lastIndex == sql.length() - 1) {
			sql = sql.substring(0, lastIndex);
		}
		if(keyword!=null && !"".equals(keyword)){
			// TODO sqlServer中文查询势必有问题 需要传入数据源类型
			String likeSql = CgformUtil.SQL_LIKE + "'%"+keyword+"%'";
			//TODO 拼接sql存在SQL注入风险
			sql = "select * from (" + sql + ") t where t.value " + likeSql  + CgformUtil.SQL_OR + "t.text" + likeSql;
		}
		IPage<Map<String,Object>> pageList = this.baseMapper.selectPageBySql(page, sql);
		List<Map<String,Object>> queryDictList = pageList.getRecords();
		//update--begin--autor:scott-----date:20220124------for：过滤空值数据，以免前端下拉报错
		queryDictList = queryDictList.stream().filter(x -> x!=null).collect(Collectors.toList());
		//update--end--autor:scott-----date:20220124------for：过滤空值数据，以免前端下拉报错

		if (queryDictList != null && queryDictList.size() != 0) {
			String queryDictJson = JSON.toJSONString(queryDictList);
			ls = JSON.parseArray(queryDictJson, DictModel.class);
		}
		return ls;
	}

	@Override
	@Cacheable(value = CacheConstant.ONLINE_RP, key = "'column-'+#code+'-'+#queryDict")
	public Map<String, Object> queryColumnInfo(String code, boolean queryDict) {
		Map<String, Object> resultMap = new HashMap<>();
		QueryWrapper<OnlCgreportItem> queryWrapper = new QueryWrapper<>();
		queryWrapper.eq("cgrhead_id", code).eq("is_show", 1).orderByAsc("order_num");
		// 获取CgreportItem列
		List<OnlCgreportItem> list = this.onlCgreportItemService.list(queryWrapper);
		// 用于在页面上遍历 href slot
		JSONArray fieldHrefSlots = new JSONArray();
		// 构造 columns
		JSONArray columns = new JSONArray();
		// 字典信息
		Map<String, Object> dictOptions = new HashMap<>();
		//是否是组合标题
		boolean isGroupTitle = false;
		for (OnlCgreportItem item : list) {
			JSONObject column = new JSONObject(4);
			column.put("title", item.getFieldTxt());
			column.put("dataIndex", item.getFieldName());
			//update--begin--autor:scott-----date:20210220------for：issues/2279 长整形时合计不显示.00后缀
			column.put("fieldType", item.getFieldType());
			//update--end--autor:scott-----date:20210220------for：issues/2279 长整形时合计不显示.00后缀
			column.put("align", "center");
			column.put("sorter", "true");
			column.put("isTotal",item.getIsTotal());
			// update-begin--Author:lvdandan  Date:20201113  for：LOWCOD-355 【优化】online报表支持多表头配置
			column.put("groupTitle",item.getGroupTitle());
			if(StringUtil.isNotEmpty(item.getGroupTitle())){
				isGroupTitle = true;
			}
			String dbtype = item.getFieldType();
			if("Integer".equals(dbtype)||"Date".equals(dbtype)||"Long".equals(dbtype)) {
				column.put("sorter","true");
			}
			// update-end--Author:lvdandan  Date:20201113  for：LOWCOD-355 【优化】online报表支持多表头配置
			// update-begin--Author:sunjianlei  Date:20200103  for：判断有没有传href，如果传了就用slot
			if (org.apache.commons.lang.StringUtil.isNotBlank(item.getFieldHref())) {
				String slotName = "fieldHref_" + item.getFieldName();
				JSONObject scopedSlots = new JSONObject();
				scopedSlots.put("customRender", slotName);
				column.put("scopedSlots", scopedSlots);

				JSONObject slot = new JSONObject();
				slot.put("slotName", slotName);
				slot.put("href", item.getFieldHref());
				fieldHrefSlots.add(slot);
			}
			// update-end--Author:sunjianlei  Date:20200103  for：判断有没有传href，如果传了就用slot

			//字典项加载
			String dictCode = item.getDictCode();
			if(dictCode!=null && !"".equals(dictCode)){
				if(queryDict){
					List<DictModel> ls = this.queryColumnDict(item.getDictCode(), null, null);
					dictOptions.put(item.getFieldName(),ls);
					column.put("customRender", item.getFieldName());
				}else{
					column.put("dictCode", dictCode);
				}
			}
			columns.add(column);
		}
		if(queryDict){
			resultMap.put("dictOptions", dictOptions);
		}
		resultMap.put("columns", columns);//列信息
		resultMap.put("fieldHrefSlots", fieldHrefSlots);//href信息
		resultMap.put("isGroupTitle",isGroupTitle);
		return resultMap;
	}

	@Override
	public List<DictModel> queryColumnDict(String dictCode, JSONArray records, String fieldName) {
		List<DictModel> ls = null;
		if (StringUtil.isNotEmpty(dictCode)) {
			// 判断字典是否是 select 开头的，如果是就认定为是一个 sql 语句，执行该语句获取数据并当做字典使用
			if (dictCode.trim().toLowerCase().indexOf("select ") == 0 && (fieldName==null || records.size() > 0) ) {
				// 此处原来的判断是dictCode.toLowerCase().indexOf("select ") == 0 && records.size() > 0
				//但是可以传null的records,

				// 去除空格和最后一个分号
				dictCode = dictCode.trim();
				int lastIndex = dictCode.lastIndexOf(";");
				if (lastIndex == dictCode.length() - 1) {
					dictCode = dictCode.substring(0, lastIndex);
				}
				// 拼接查询SQL
				String selectSql = "SELECT * FROM (" + dictCode + ") temp ";
				// 只取需要的值，防止字典SQL一下查询出所有的数据
				if(records!=null){
					HashSet<String> inValues = new HashSet<>();
					for (int i = 0; i < records.size(); i++) {
						JSONObject data = records.getJSONObject(i);
						String value = data.getString(fieldName);
						if (org.apache.commons.lang.StringUtil.isNotBlank(value)) {
							inValues.add(value);
						}
					}
					String inValueStr = "'" + StringUtils.join(inValues, "','") + "'";
					selectSql += "WHERE temp.value IN (" + inValueStr + ")";
				}
				List<Map<?, ?>> queryDictList = this.getBaseMapper().executeSelete(selectSql);
				if (queryDictList != null && queryDictList.size() != 0) {
					String queryDictJson = JSON.toJSONString(queryDictList);
					ls = JSON.parseArray(queryDictJson, DictModel.class);
				}
			} else {
				ls = sysBaseAPI.queryDictItemsByCode(dictCode);
			}
		}
		return ls;
	}

	@Override
	public List<DictModel> queryColumnDictList(String dictCode, List<Map<String, Object>> records, String fieldName) {
		List<DictModel> ls = null;
		if (StringUtil.isNotEmpty(dictCode)) {
			dictCode = dictCode.trim();
			if (dictCode.toLowerCase().indexOf("select ") == 0 && (fieldName==null || records.size() > 0) ) {
				if(dictCode.endsWith(";")){
					dictCode = dictCode.substring(0, dictCode.length()-1);
				}
				// 拼接查询SQL
				String selectSql = "SELECT * FROM (" + dictCode + ") temp ";
				if(records!=null){
					if(records.size()<100){
						// 如果数据量超出100 其实也没必要in了 直接查询所有的吧
						HashSet<String> inValues = new HashSet<>();
						for (int i = 0; i < records.size(); i++) {
							Map<String, Object> row = records.get(i);
							if(row==null){
								continue;
							}
							Object data = CgformUtil.getMapValue(row, fieldName);
							if(data==null){
								continue;
							}
							inValues.add(data.toString());
						}
						String inValueStr = "'" + StringUtils.join(inValues, "','") + "'";
						selectSql += "WHERE temp.value IN (" + inValueStr + ")";
					}
				}
				ls = this.getBaseMapper().queryDictListBySql(selectSql);
			}else {
				ls = sysBaseAPI.queryDictItemsByCode(dictCode);
			}
		}
		return ls;
	}


}
